package com.hgj.dao.impl;

import com.hgj.dao.GoodsDao;
import com.hgj.entity.Goods;
import com.hgj.entity.User;
import com.hgj.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class GoodsDaoImpl implements GoodsDao {
    //1.获取连接
    Connection conn=DBUtil.getConnection();
    //2.创建执行sql工具类对象
    QueryRunner qr=new QueryRunner();
    @Override
    public int save(Goods obj) {
        //3.定义sql语句
        String sql="INSERT INTO  tb_goods(goods_id,goods_name,goods_price,goods_sales,goods_date,goods_type,goods_intro,goods_img) VALUES (?,?,?,?,?,?,?,?)";
        //4.传递参数是一个数组 按照占位符顺序传参
        Object[] paras={obj.getGoods_id(),
                obj.getGoods_name(),
                obj.getGoods_price(),
                obj.getGoods_sales(),
                obj.getGoods_date(),
                obj.getGoods_type(),
                obj.getGoods_intro(),
                obj.getGoods_img()
        };
        //5.更新到数据库操作
        //update(连接,sql语句,参数)
        try {
            int row=qr.update(conn,sql,paras);
            return  row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int update(Goods obj) {
        //3.定义sql语句
        String sql="UPDATE  tb_goods SET  goods_name=?,goods_price=?,goods_sales=?,goods_date=?,goods_type=?,goods_intro=?,goods_img=? WHERE goods_id=? ";
        //4.传递参数是一个数组 按照占位符顺序传参
        Object[] paras={
                obj.getGoods_id(),
                obj.getGoods_name(),
                obj.getGoods_price(),
                obj.getGoods_sales(),
                obj.getGoods_date(),
                obj.getGoods_type(),
                obj.getGoods_intro(),
                obj.getGoods_img()
        };
        //5.更新到数据库操作
        //update(连接,sql语句,参数)
        try {
            int row=qr.update(conn,sql,paras);
            return  row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(Object id) {
        String sql="DELETE FROM tb_goods WHERE goods_id  = ? ";
        Object[] paras={id};
        try {
            int row=qr.update(conn,sql,paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public Goods findById(Object id) {
        String sql="select * from tb_goods " +
                "  where goods_id=?";
        Object[] paras={id};
        try {
            //BeanHandler<类型>(类型.class)
            //转换为类型的对象
            Goods obj=qr.query(conn,sql,paras,
                    new BeanHandler<Goods>(Goods.class));
            return  obj;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Goods> findAll() {
        String sql="select * from tb_goods";
        Object[] paras={}; //参数
        try {
            //转换器
            //BeanListHandler<类型>(类型.class)
            //将查询结果自动转换为当前类型的List集合
            List<Goods> goodsList=
                    qr.query(conn,sql,paras,
                            new BeanListHandler<Goods>(Goods.class));
            return goodsList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Goods> findSearch(String keyword) {
        String sql="select * from tb_goods where goods_name like concat(concat('%',?),'%')";
        Object[] paras={keyword};
        try {
            List<Goods> goodsList=qr.query(conn,sql,paras,new BeanListHandler<Goods>(Goods.class));
            return goodsList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Goods> findPage(int page, int pagesize) {
        String sql="select * from tb_goods " +
                "  limit ?,? ";
        int start=(page-1)*pagesize;
        Object[] paras={start,pagesize};
        try {
            List<Goods> goodsList=qr.query(conn,sql,paras,
                    new BeanListHandler<Goods>(Goods.class));
            return  goodsList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public long count() {
        String sql="select count(*) from tb_user";
        Object[] paras={};
        try {
            //ScalarHandler<类型>()
            //返回单一数据类型的数值
            //例如Integer Long Float ...
            long nums=qr.query(conn,sql,paras,
                    new ScalarHandler<Long>());
            return nums;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Goods> find(Map<String, Object> map) {
        String sql = "select * from tb_goods  where 1=1 ";
        Object[] para = {};
        if (map.get("goods_type") != null) {
            sql = sql + " and   goods_type =? ";
            para = new Object[]{map.get("goods_type").toString()};
        }
        try {
            List<Goods> goodsList = qr.query(conn, sql, para, new BeanListHandler<Goods>(Goods.class));
            return goodsList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<String> findGoods() {
        String sql="select distinct goods_type from tb_goods";
        Object[] paras={};//无？ 不需要参数
        try {
            List<String> stringList=qr.query(conn,sql,paras,
                    new ColumnListHandler<String>());
            return stringList ;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
